#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
create database IF NOT EXISTS edu_rpt;

--客户意向主题
--每天/每月/每年线上线下以及新老学员的意向用户个数
DROP TABLE IF EXISTS edu_rpt.rpt_customer_cnt_day_month_year;
create table edu_rpt.rpt_customer_cnt_day_month_year(
    date_time      STRING COMMENT '统计日期，那一天干活的',
    year_code      STRING COMMENT '年，如2023',
    year_month     STRING COMMENT '年月，如202305',
    year_month_day STRING COMMENT '年月日，如20230508',

    time_type      STRING COMMENT '分组类型:date,month,year',
    group_type     STRING COMMENT '分组类型:area,depart,clue_state,origin_type,sub,school,all',

    clue_state     STRING COMMENT '新老学员',
    origin_type    STRING COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',
    id_count       BIGINT COMMENT '意向用户个数'
)
COMMENT '每天/每月/每年线上线下以及新老学员的意向用户个数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


DROP TABLE IF EXISTS edu_rpt.rpt_area_customer_cnt_day_month_year;
-- 每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
create table edu_rpt.rpt_area_customer_cnt_day_month_year(
    date_time      STRING COMMENT '统计日期，那一天干活的',
    year_code      STRING COMMENT '年，如2023',
    year_month     STRING COMMENT '年月，如202305',
    year_month_day STRING COMMENT '年月日，如20230508',

    time_type      STRING COMMENT '分组类型:date,month,year',
    group_type     STRING COMMENT '分组类型:area,depart,clue_state,origin_type,sub,school,all',

    area           STRING COMMENT '地区',

    clue_state     STRING COMMENT '新老学员',
    origin_type    STRING COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',
    id_count       BIGINT COMMENT '意向用户个数'
)
COMMENT '每天/每月/每年各地区的线上线下以及新老学员的意向用户个数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


DROP TABLE IF EXISTS edu_rpt.rpt_subject_customer_cnt_day_month_year;
-- 每天/每月/每年 各学科 线上线下以及新老学员的意向用户个数Top10
create table edu_rpt.rpt_subject_customer_cnt_day_month_year(
    date_time      STRING COMMENT '统计日期，那一天干活的',
    year_code      STRING COMMENT '年，如2023',
    year_month     STRING COMMENT '年月，如202305',
    year_month_day STRING COMMENT '年月日，如20230508',

    time_type      STRING COMMENT '分组类型:date,month,year',
    group_type     STRING COMMENT '分组类型:area,depart,clue_state,origin_type,sub,school,all',

    sub_id         INT COMMENT '学科id',
    sub_name       STRING COMMENT '学科名称',

    clue_state     STRING COMMENT '新老学员',
    origin_type    STRING COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',

    id_count       BIGINT COMMENT '意向用户个数'
)
COMMENT '每天/每月/每年 各学科 线上线下以及新老学员的意向用户个数Top10'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


DROP TABLE IF EXISTS edu_rpt.rpt_school_customer_cnt_day_month_year;
-- 每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10
create table edu_rpt.rpt_school_customer_cnt_day_month_year(
    date_time      STRING COMMENT '统计日期，那一天干活的',
    year_code      STRING COMMENT '年，如2023',
    year_month     STRING COMMENT '年月，如202305',
    year_month_day STRING COMMENT '年月日，如20230508',

    time_type      STRING COMMENT '分组类型:date,month,year',
    group_type     STRING COMMENT '分组类型:area,depart,clue_state,origin_type,sub,school,all',

    school_id      INT COMMENT '校区id',
    school_name    STRING COMMENT '校区名称',
    clue_state     STRING COMMENT '新老学员',
    origin_type    STRING COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',
    id_count       BIGINT COMMENT '意向用户个数'
)
COMMENT '每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


DROP TABLE IF EXISTS edu_rpt.rpt_origin_channel_customer_cnt_day_month_year;
-- 每天/每月/每年各来源渠道线上线下以新老学员的意向用户个数
create table edu_rpt.rpt_origin_channel_customer_cnt_day_month_year(
    date_time      STRING COMMENT '统计日期，那一天干活的',
    year_code      STRING COMMENT '年，如2023',
    year_month     STRING COMMENT '年月，如202305',
    year_month_day STRING COMMENT '年月日，如20230508',

    time_type      STRING COMMENT '分组类型:date,month,year',
    group_type     STRING COMMENT '分组类型:area,depart,clue_state,origin_type,sub,school,all',

    origin_channel STRING COMMENT '来源渠道',

    clue_state     STRING COMMENT '新老学员',
    origin_type    STRING COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',
    id_count       BIGINT COMMENT '意向用户个数'
)
COMMENT '每天/每月/每年各来源渠道线上线下以新老学员的意向用户个数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


DROP TABLE IF EXISTS edu_rpt.rpt_depart_customer_cnt_day_month_year;
-- 每天/每月/每年各咨询中心线上线下以及新老学员的意向用户个数
create table edu_rpt.rpt_depart_customer_cnt_day_month_year(
    date_time      STRING COMMENT '统计日期，那一天干活的',
    year_code      STRING COMMENT '年，如2023',
    year_month     STRING COMMENT '年月，如202305',
    year_month_day STRING COMMENT '年月日，如20230508',

    time_type      STRING COMMENT '分组类型:date,month,year',
    group_type     STRING COMMENT '分组类型:area,depart,clue_state,origin_type,sub,school,all',

    depart_id      INT COMMENT '部门id -> 咨询中心id',
    depart_name    STRING COMMENT '部门名称 -> 咨询中心名称',

    clue_state     STRING COMMENT '新老学员',
    origin_type    STRING COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',
    id_count       BIGINT COMMENT '意向用户个数'
)
COMMENT '每天/每月/每年各咨询中心线上线下以及新老学员的意向用户个数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


DROP TABLE IF EXISTS edu_rpt.rpt_effective_customer_cnt_day;
-- 每天线上线下及新老学员的有效线索个数
create table edu_rpt.rpt_effective_customer_cnt_day(
    date_time                STRING COMMENT '统计日期，那一天干活的',
    dt                       string COMMENT '每天',
    group_type               string COMMENT '分组类型:time_hour,dt',
    clue_state               STRING COMMENT '新老学员',
    origin_type              STRING COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',
    appeal_status            TINYINT COMMENT '申诉状态，0:待稽核 1:无效 2：有效',

    first_id_effective_count BIGINT COMMENT '有效线索个数'
)
COMMENT '每天线上线下及新老学员的有效线索个数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


DROP TABLE IF EXISTS edu_rpt.rpt_effective_ratio_customer_datetime;
-- 每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
create table edu_rpt.rpt_effective_ratio_customer_datetime(
    date_time                STRING COMMENT '统计日期，那一天干活的',
    datetime                 STRING COMMENT '小时',
    group_type               string COMMENT '分组类型:time_hour,dt',

    clue_state               STRING COMMENT '新老学员',
    origin_type              STRING COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',
    appeal_status            TINYINT COMMENT '申诉状态，0:待稽核 1:无效 2：有效',

    first_id_effective_count BIGINT COMMENT '有效线索个数',
    first_id_count           BIGINT COMMENT '总线索个数',
    effective_count_ratio    DECIMAL(5,2) COMMENT '有效线索转化率:有效线索个数 / 总线索个数'
)
COMMENT '每小时线上线下及新老学员的有效线索转化率'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');
"